#!/usr/bin/python
# coding:utf-8

import os
import configparser
import db.PyMysql2
from sqlalchemy import create_engine


def create_conn():
    '''
    读取配置文件
    '''
    file_dir = os.path.split(os.path.realpath(__file__))[0]
    home_dir = os.path.split(file_dir)[0]
    int_file = home_dir + "/config.ini"

    cf = configparser.ConfigParser()
    cf.read(int_file)

    # 读取环境相关配置
    cfg_env = cf.get("env_params", "env")

    if cfg_env == "DEV":
        datebase_env = "dev_database"
    else:
        datebase_env = "prd_database"

    # 读取数据库相关配置
    hostname = cf.get(datebase_env, "hostname")
    port = int(cf.get(datebase_env, "port"))
    username = cf.get(datebase_env, "username")
    password = cf.get(datebase_env, "password")
    db_name = cf.get(datebase_env, "db_name")

    # 建立连接
    conn = db.PyMysql2.PyMysql2()
    conn.newConnection(host=hostname, port=port, user=username, passwd=password, db=db_name)
    return conn


def close_conn(conn):
    # 关闭连接
    conn.closeConnnection()


def create_db_engine():
    '''
    读取配置文件
    '''
    file_dir = os.path.split(os.path.realpath(__file__))[0]
    home_dir = os.path.split(file_dir)[0]
    int_file = home_dir + "/config.ini"

    cf = configparser.ConfigParser()
    cf.read(int_file)

    # 读取环境相关配置
    cfg_env = cf.get("env_params", "env")

    if cfg_env == "DEV":
        datebase_env = "dev_database"
    else:
        datebase_env = "prd_database"

    # 读取数据库相关配置
    hostname = cf.get(datebase_env, "hostname")
    port = int(cf.get(datebase_env, "port"))
    username = cf.get(datebase_env, "username")
    password = cf.get(datebase_env, "password")
    db_name = cf.get(datebase_env, "db_name")

    # 建立连接
    link_str = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(username, password, hostname,port, db_name)
    engine = create_engine(link_str)
    return engine

def close_db_engine(engine):
    # 关闭连接
    engine.dispose()



def save_data_to_mysql(engine, df, table_name):
    """
    将一个DataFrame保存至数据库

    参数说明:
        df: 一个DataFrame对象
        table_name: 需要存入数据库的表名,字符串类型
    """
    try:
        # ms_engine = create_db_engine()
        df.to_sql(table_name, engine, if_exists='append', index=False)
    except Exception as e:
        raise Exception("保存数据时发生错误：" + str(e))
    finally:
        # engine.dispose()
        pass
#
# df_res = pd.DataFrame([[1, 2, 3, 4],
#                        [5, 6, 7, 8],
#                        [9, 10, 11, 12]
#                        ], columns=['A', 'B', 'C', 'D'])
#
# # 将df_res数据存入到test数据库的df_result表中
# save_data_to_mysql(df_res, 'df_result')